using System.Data;
using System.Data.OleDb;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using DataTable=System.Data.DataTable;

namespace DBRestriker.IO
{
	internal class ExcelReader
	{
		private readonly string _fileName;

		internal ExcelReader(string fileName)
		{
			_fileName = fileName;
		}

		internal string FileName
		{
			get { return _fileName; }
		}

		internal DataTable GetDataTable(int sheetIndex)
		{
			return Connect(FileName, GetSheetName(sheetIndex));
		}

		internal string GetSheetName(int sheetIndex)
		{
			Application excelApp = new Application();
			string sheetName;
			try
			{
				Workbook excelWorkbook = excelApp.Workbooks.Open(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
				Worksheet worksheet = (Worksheet) excelWorkbook.Worksheets[sheetIndex];
				sheetName = worksheet.Name;
			}
			finally
			{
				excelApp.Workbooks.Close();
			}
			return sheetName;
		}

		private static DataTable Connect(string fileName, string sheetName)
		{
			DataTable dataTable = null;
			OleDbConnection oleDbConnection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", fileName));
			try
			{
				OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}$]", sheetName), oleDbConnection);
				DataSet dataSet = new DataSet();
				oleDbDataAdapter.Fill(dataSet, sheetName);
				DataTableCollection dataTableCollection = dataSet.Tables;
				if (0 < dataTableCollection.Count)
				{
					DataTable sourceDataTable = dataTableCollection[0];
					dataTable = new DataTable();

					// Copy columns
					foreach (DataColumn sourceDataColumn in sourceDataTable.Columns)
					{
						DataColumn dataColumn = new DataColumn();
						dataColumn.AutoIncrement = false;
						dataColumn.Caption = sourceDataColumn.Caption;
						dataColumn.ColumnName = sourceDataColumn.ColumnName;
						dataColumn.DataType = typeof(string); //sourceDataColumn.DataType;
						dataColumn.ReadOnly = sourceDataColumn.ReadOnly;
						dataColumn.Unique = sourceDataColumn.Unique;

						dataTable.Columns.Add(dataColumn);
					}

					// Import rows
					foreach (DataRow dataRow in sourceDataTable.Rows)
					{
						dataTable.ImportRow(dataRow);
					}
				}
			}
			finally
			{
				oleDbConnection.Close();
			}
			return dataTable;
		}
	}
}